/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.bionic.dao;

import com.bionic.entity.Account;
import com.bionic.idao.IReqestDAO;
import com.bionic.entity.AccountType;
import com.bionic.entity.Currency;
import com.bionic.entity.Reqest;
import com.bionic.entity.ReqestStatus;
import com.bionic.entity.ReqestType;
import com.bionic.entity.Users;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import com.bionic.jdbc.connection.DAOFactory;

/**
 *
 * @author Obuhov Denis
 */
public class ReqestDAO implements IReqestDAO {

    @Override
    public ArrayList<Reqest> findAll() {
             final String QUERY = "SELECT"
                     + " reqest.id"
                     + " ,reqest.user_id"
                     + " ,reqest.status_id"
                     + " ,reqest.reqest_type_id"
                     + " ,reqest.amount_from"
                     + " ,reqest.currency_from_id"
                     + " ,reqest.currency_to_id"
                     + " ,reqest.create_date"
                     + " ,reqest.expire_date"
                     + " ,reqest.close_date"
                     + " FROM mydb.reqest";
             
        ArrayList<Reqest> requestLst = new ArrayList<Reqest>();
        Reqest request = null;
        Users user = null;
        Currency currency_from = null;
        Currency currency_to = null;
        ReqestStatus  requestStatus = null;
        ReqestType requestType = null;

        Connection connection = null;
        PreparedStatement ptmt = null;
        ResultSet resultSet = null;

        try {
            connection = DAOFactory.createConnection();
            ptmt = connection.prepareStatement(QUERY);
            resultSet = ptmt.executeQuery();

            while (resultSet.next()) {
                // создали ссылочне объекты, у которых заполнен только ID
                request = new Reqest(resultSet.getInt("id"));
                user = new Users(resultSet.getInt("user_id"));
                request.setUserId(user);
                requestStatus = new ReqestStatus(resultSet.getInt("status_id"));
                request.setStatusId(requestStatus);
                requestType = new ReqestType(resultSet.getInt("reqest_type_id"));
                request.setReqestTypeId(requestType);
                request.setAmountFrom(resultSet.getBigDecimal("amount_from"));
                currency_from = new Currency(resultSet.getInt("currency_from_id"));
                request.setCurrencyFromId(currency_from);
                currency_to = new Currency(resultSet.getInt("currency_to_id"));
                request.setCurrencyToId(currency_to);
                request.setCreateDate(resultSet.getDate("create_date"));
                request.setExpireDate(resultSet.getDate("expire_date"));
                request.setExpireDate(resultSet.getDate("close_date"));
                
                // добавили полученный объект в список
                requestLst.add(request);
            }

        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class
                    .getName()).log(Level.SEVERE, null, ex);
        }
        return requestLst;

    }


    @Override
    public Reqest findById(Integer id) {
          final String QUERY = "SELECT"
                     + " reqest.id"
                     + " ,reqest.user_id"
                     + " ,reqest.status_id"
                     + " ,reqest.reqest_type_id"
                     + " ,reqest.amount_from"
                     + " ,reqest.currency_from_id"
                     + " ,reqest.currency_to_id"
                     + " ,reqest.create_date"
                     + " ,reqest.expire_date"
                     + " ,reqest.close_date"
                     + " FROM mydb.reqest where reqest.id = ?";
        Account account = null;
        Users user = null;
        AccountType accountType = null;
        Currency currency = null;
        Reqest someRequest = null;
        Connection connection = null;
        PreparedStatement ptmt = null;
        ResultSet resultSet = null;
        ReqestStatus  requestStatus = null;
        ReqestType requestType = null;
        Currency currency_from = null;
        Currency currency_to = null;

        try {
            connection = DAOFactory.createConnection();
            ptmt = connection.prepareStatement(QUERY);
            ptmt.setInt(1, id);
            resultSet = ptmt.executeQuery();

            while (resultSet.first()) {
                     // создали ссылочне объекты, у которых заполнен только ID
                someRequest = new Reqest(resultSet.getInt("id"));
                user = new Users(resultSet.getInt("user_id"));
                someRequest.setUserId(user);
                requestStatus = new ReqestStatus(resultSet.getInt("status_id"));
                someRequest.setStatusId(requestStatus);
                requestType = new ReqestType(resultSet.getInt("reqest_type_id"));
                someRequest.setReqestTypeId(requestType);
                someRequest.setAmountFrom(resultSet.getBigDecimal("amount_from"));
                currency_from = new Currency(resultSet.getInt("currency_from_id"));
                someRequest.setCurrencyFromId(currency_from);
                currency_to = new Currency(resultSet.getInt("currency_to_id"));
                someRequest.setCurrencyToId(currency_to);
                someRequest.setCreateDate(resultSet.getDate("create_date"));
                someRequest.setExpireDate(resultSet.getDate("expire_date"));
                someRequest.setExpireDate(resultSet.getDate("close_date"));
                
            }

        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class
                    .getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("  Reqest findById\n"+someRequest);
        return someRequest;
    }

    @Override
    public Integer update(Reqest reqest) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public Integer insert(Reqest reqest) {
          final String QUERY =
                  "INSERT INTO mydb.reqest"
                  + "(id,user_id,status_id,reqest_type_id,amount_from,currency_from_id,currency_to_id,"
                  + " create_date,expire_date,close_date)"
                  + "VALUES"
                  + "(mydb.reqest_seq() ,?,?,?,?,?,?,SYSDATE(),NULL,NULL)";

        Integer rowInsert = null;
        Users someUser = null;
        AccountType accountType = null;
        Currency currency = null;

        Connection connection = null;
        PreparedStatement ptmt = null;
        ResultSet resultSet = null;

        try {
            connection = DAOFactory.createConnection();
            ptmt = connection.prepareStatement(QUERY);
            ptmt.setInt(1, reqest.getUserId().getId());
            ptmt.setInt(2, reqest.getStatusId().getId());
            ptmt.setInt(3, reqest.getReqestTypeId().getId());
            ptmt.setBigDecimal(4, reqest.getAmountFrom());
            ptmt.setInt(5, reqest.getCurrencyFromId().getId());
            ptmt.setInt(6, reqest.getCurrencyToId().getId());
           // ptmt.setDate(7, reqest.getExpireDate());
            
       
            rowInsert = ptmt.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class
                    .getName()).log(Level.SEVERE, null, ex);
        }
        System.out.println("  Request insert rowUpdate="+rowInsert+"\n"+reqest); 
        return rowInsert;
    }

    @Override
    public Integer deleteById(Integer id) {
        throw new UnsupportedOperationException("Not supported yet.");
    }





    @Override
    public ArrayList<Reqest> findByUser(Users user) { 
             final String QUERY = "SELECT"
                     + " reqest.id"
                     + " ,reqest.user_id"
                     + " ,reqest.status_id"
                     + " ,reqest.reqest_type_id"
                     + " ,reqest.amount_from"
                     + " ,reqest.currency_from_id"
                     + " ,reqest.currency_to_id"
                     + " ,reqest.create_date"
                     + " ,reqest.expire_date"
                     + " ,reqest.close_date"
                     + " FROM mydb.reqest where reqest.user_id = ?";
             
        ArrayList<Reqest> requestLst = new ArrayList<Reqest>();
        Reqest request = null;
        Currency currency_from = null;
        Currency currency_to = null;
        ReqestStatus  requestStatus = null;
        ReqestType requestType = null;

        Connection connection = null;
        PreparedStatement ptmt = null;
        ResultSet resultSet = null;

        try {
            connection = DAOFactory.createConnection();
            ptmt = connection.prepareStatement(QUERY);
            ptmt.setInt(1, user.getId());
            resultSet = ptmt.executeQuery();

            while (resultSet.next()) {
                // создали ссылочне объекты, у которых заполнен только ID
                request = new Reqest(resultSet.getInt("id"));
                user = new Users(resultSet.getInt("user_id"));
                request.setUserId(user);
                requestStatus = new ReqestStatus(resultSet.getInt("status_id"));
                request.setStatusId(requestStatus);
                requestType = new ReqestType(resultSet.getInt("reqest_type_id"));
                request.setReqestTypeId(requestType);
                request.setAmountFrom(resultSet.getBigDecimal("amount_from"));
                currency_from = new Currency(resultSet.getInt("currency_from_id"));
                request.setCurrencyFromId(currency_from);
                currency_to = new Currency(resultSet.getInt("currency_to_id"));
                request.setCurrencyToId(currency_to);
                request.setCreateDate(resultSet.getDate("create_date"));
                request.setExpireDate(resultSet.getDate("expire_date"));
                request.setExpireDate(resultSet.getDate("close_date"));
                
                // добавили полученный объект в список
                requestLst.add(request);
            }

        } catch (SQLException ex) {
            Logger.getLogger(UserDAO.class
                    .getName()).log(Level.SEVERE, null, ex);
        }
        return requestLst;
    }
    
}
